package com.climb.moive.dao.impl;

import com.climb.moive.dao.MovieReviewDao;
import com.climb.moive.entity.MovieReview;
import com.climb.moive.exception.ApplicationException;
import com.climb.moive.framework.jdbc.BeanPropertyRowMapper;
import com.climb.moive.framework.jdbc.JdbcTemplate;
import com.climb.moive.framework.jdbc.RowMapper;
import com.climb.moive.util.JdbcUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class MovieReviewDaoImpl implements MovieReviewDao {
    @Override
    public Integer count() throws SQLException {
        String sql="SELECT COUNT(1) from movie_review";
        Connection con = null;
        Integer result=0;
        try {
            con = JdbcUtils.getConnection();
            JdbcTemplate jdbcTemplate = new JdbcTemplate(con);
            result=jdbcTemplate.queryForObject(sql,Integer.class);

        } finally {
            JdbcUtils.close(con);
        }
        return result;
    }

    @Override
    public Integer countById(Integer id) throws SQLException {
        String sql="SELECT COUNT(1) from movie_review where `mem_id`=?";
        Connection con = null;
        Integer result=0;
        try {
            con = JdbcUtils.getConnection();
            JdbcTemplate jdbcTemplate = new JdbcTemplate(con);
            result=jdbcTemplate.queryForObject(sql,Integer.class,id);
        } finally {
            JdbcUtils.close(con);
        }
        return result;
    }

    @Override
    public Integer delet(Integer mid) throws SQLException {
        String sql="delete FROM movie_review where `mem_id`=?";
        Connection con = null;
        Integer result=0;
        try {
            con = JdbcUtils.getConnection();
            JdbcTemplate jdbcTemplate = new JdbcTemplate(con);
            result=jdbcTemplate.update(sql,mid);
        } finally {
            JdbcUtils.close(con);
        }
        return result;
    }

    @Override
    public Integer deletByReviewId(Integer rid) throws SQLException {
        String sql="DELETE FROM `movie_review` WHERE `id`=?";
        Connection connection=null;
        Integer result=null;
        try {
            connection=JdbcUtils.getDataSource().getConnection();
            JdbcTemplate jdbcTemplate=new JdbcTemplate(connection);
            result=  jdbcTemplate.update(sql,rid);
        }  finally {
            JdbcUtils.close(connection);
        }
        return result;
    }

    @Override
    public MovieReview findRevieByRid(Integer rid) throws SQLException {
        String sql="SELECT * from movie_review where id=?";
        Connection con = null;
        MovieReview review=null;
        try {
            con = JdbcUtils.getConnection();
            JdbcTemplate jdbcTemplate = new JdbcTemplate(con);
            RowMapper<MovieReview> reviewRowMapper=new BeanPropertyRowMapper<>(MovieReview.class);
            review=jdbcTemplate.queryForObject(sql,reviewRowMapper,rid);
        } catch (SQLException throwables) {
            new ApplicationException("数据访问层查询文章失败",throwables);
        } finally {
            JdbcUtils.close(con);
        }
        return review;
    }

    @Override
    public List<MovieReview> findAllMovieReview() throws SQLException {
        String sql="SELECT * from movie_review";
        Connection con = null;
        List<MovieReview> reviews=null;
        try {
            con = JdbcUtils.getConnection();
            JdbcTemplate jdbcTemplate = new JdbcTemplate(con);
            RowMapper<MovieReview> rowMapper=new BeanPropertyRowMapper<>(MovieReview.class);
            reviews=jdbcTemplate.query(sql,rowMapper);

        } catch (SQLException throwables) {
            new ApplicationException("数据访问层查询所有文章失败",throwables);
        } finally {
            JdbcUtils.close(con);
        }
        return reviews;
    }

    @Override
    public List<MovieReview> findReviewByMoiveid(Integer id) throws SQLException {
        String sql="SELECT * from movie_review where mov_id=?  order by publish_time desc";
        Connection con = null;
        List<MovieReview> reviews=null;
        try {
            con = JdbcUtils.getConnection();
            JdbcTemplate jdbcTemplate = new JdbcTemplate(con);
            RowMapper<MovieReview> rowMapper=new BeanPropertyRowMapper<>(MovieReview.class);
            reviews=jdbcTemplate.query(sql,rowMapper,id);

        } catch (SQLException throwables) {
            new ApplicationException("数据访问层查询所有文章bymovieId失败",throwables);
        } finally {
            JdbcUtils.close(con);
        }
        return reviews;
    }

    @Override
    public Integer saveMovieReview(MovieReview movieReview) throws SQLException {
        String sql="INSERT INTO `movie_review` (`mov_id`, `mem_id`, `score`, `title`, `content`, `author`, `publish_time`) VALUES (?,?,?,?,?,?,?)";
        Integer result=null;
        Connection connection=null;
        try {
            connection=JdbcUtils.getConnection();
            JdbcTemplate jdbcTemplate=new JdbcTemplate(connection);
            result= jdbcTemplate.update(sql,movieReview.getMov_id(),movieReview.getMem_id(),movieReview.getScore(),movieReview.getTitle(),movieReview.getContent(),movieReview.getAuthor(),movieReview.getPublish_time());
        } finally {
            JdbcUtils.close(connection);
        }
        return result;
    }

    @Override
    public List<MovieReview> findReviewByMemberId(Integer id) throws SQLException {
        String sql="SELECT r.id ,m.name, r.score,r.title from movie_review r INNER JOIN movie m ON m.id=r.mov_id where mem_id=? order by publish_time desc";

        Connection con = null;
        List<MovieReview> reviews=null;
        try {
            con = JdbcUtils.getConnection();
            JdbcTemplate jdbcTemplate = new JdbcTemplate(con);
            RowMapper<MovieReview> rowMapper=new BeanPropertyRowMapper<>(MovieReview.class);
            reviews=jdbcTemplate.query(sql,rowMapper,id);

        } catch (SQLException throwables) {
            new ApplicationException("数据访问层查询所有文章bymovieId失败",throwables);
        } finally {
            JdbcUtils.close(con);
        }
        return reviews;
    }


}
